package cn.byapps.hw.dao;

import cn.byapps.hw.dto.HomeWorkDTO;
import cn.byapps.hw.entity.Homework;
import cn.byapps.hw.entity.HomeworkFile;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface HomeworkDao extends JpaRepository<Homework, Integer> {
    /**
     * 获取课程所有作业的情况
     *
     * @param clazzId
     * @param courseId
     * @return
     */
    @Query(value = "select new cn.byapps.hw.dto.HomeWorkDTO(h.id,h.name,count(h.id)) from Homework h "
            + "left join h.serialPK pk " + "left join h.homeworkFiles s "
            + "where pk.clazz.id =:clazzId and pk.course.id =:courseId "
            + "group by h.id order by h.publishTime desc")
    List<HomeWorkDTO> getHomeWorkDTO(Integer clazzId, Integer courseId);

    /**
     * 根据课程ID和班级ID获取所有提交作业的情况
     *
     * @param clazzId
     * @param courseId
     * @return
     */
    @Query(value = "select st.name,h.name,h.id from Homework h "
            + "left join h.serialPK pk "
            + "left join h.serialPK.clazz s "
            + "left join h.serialPK.clazz.students st "
            + "left join h.homeworkFiles hf "
            + "where pk.clazz.id =:clazzId and pk.course.id =:courseId and st.id = hf.sId "
            + "order by st.name")
    List<Object[]> getAllHomeWorkFileDTO(Integer clazzId, Integer courseId);

    /**
     * 根据serialPKId获取所有作业
     *
     * @param serialPKId
     * @return
     */
    @Query("select (u) from  Homework u where u.serialPK.id = :serialPKId")
    List<Homework> findBySerialPKId(Integer serialPKId);
}
